package gmu.xytxl.dao.impl;

import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import sun.misc.BASE64Decoder;
import java.util.List;

import gmu.xytxl.bean.Maillist;
import gmu.xytxl.dao.IMaillistDAO;
import gmu.xytxl.util.JDBCUtils;

public class MaillistDAOImpl implements IMaillistDAO{

	@Override
	public List<Maillist> findAllMaillist(String page,String pagesize) {
		String sql = "WITH sss AS (SELECT *,ROW_NUMBER() "
				+ "OVER(ORDER BY name) AS rowNum FROM maillist)"
				+ " SELECT * FROM sss WHERE rowNum BETWEEN ? AND ? ";
		
		//String sqlString = "select * from Sheet1";
		
		List findlist = new ArrayList<>();
		int begin = Integer.parseInt(page);
		int end = Integer.parseInt(pagesize);
		if (begin == 1) {
			findlist.add(0,begin);
			findlist.add(1,end);
		}else {
			begin = (begin-1)*end +1;
			findlist.add(0,begin);
			end = begin+end-1;
			findlist.add(1,end);
		}
		ResultSet rs = JDBCUtils.findAll(sql, findlist);
		System.out.println(sql);
		//ResultSet rs = JDBCUtils.findAll(sqlString, null);
		List<Maillist> list = new ArrayList<>();
		try {
			while (rs.next()) {
				Maillist maillist = new Maillist();
				maillist.setName(rs.getString("name"));
				maillist.setZone(rs.getString("zone"));
				maillist.setTelephone(rs.getString("telephone"));
				maillist.setMajor(rs.getString("major"));
				maillist.setGrade(rs.getString("grade"));
				maillist.setEducation(rs.getString("education"));
				list.add(maillist);
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		//JDBCUtils.closeConnection(rs, JDBCUtils.preparedStatement, JDBCUtils.conn);
		return list;
	}

}
